Helpful Information

Category: CSV

CSV --> PHP --> MySQL

CSV --> PHP --> MySQI've got another project im going to be starting for our Intranet portal.

so far i have a working dynamic calendar system and also a web based timesheet/payroll system.

the next one is going to be a way to check stock from our warehouse without having to call them each time.

each morning they send us a CSV file with 3 columns. it is set up like this:

Item Number,Product Description,Net Available Inventory
D112000BT,1H T&S P/B Valve w stop,955
D113000BT,1H T&S P/B Valve w diverter w stop,4218
D115000BT,1H T&S P/B Valve no stop,9285

so what i need to do is have some way of using php to take the values in this file and dump them into a mysql database with 3 columns as well.

so i can have a search page and just type in the model number and it will bring up the description and how many are available.

the plan is to have a page with a browse button and you browse to the new file every morning and then submit it and it deletes everything that is currently in the mtsql database and then put in everything that is in the csv file.

wow i just found that you can do this with phpmyadmin

except that it gets hung up on some lines because they have info that is in quotes.
like this

D150000BT,"Valve w stops 1/2"" IPS w Volume Control",0

Find and replace "" with \" and '' with \'. Then use phpMyAdmin.
The cvs "spec" (if there was an official one) says to escape quotes inside quotes with another quote. Crazy 'eh?

so "Here is a " ok?"
would become "Here is a "" ok?"

wow that is weird... but it worked... it took me about an hour to finish this whole project

LOAD DATA LOCAL INFILE (http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html)... is the SQL command you want ( look for the OPTIONALLY ENCLOSED field ).

this is a little tricky... having some problems with the syntax.

<? include('../head.php'); ?>

<?php require_once('../../Connections/go.php'); ?>
<?php
$filename = "Inventory_Availability_Report.csv";
mysql_select_db($database_go, $go);
$query = "LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE '$filename'
[REPLACE | IGNORE]
INTO TABLE stock
[FIELDS
[TERMINATED BY ',']
[[OPTIONALLY] ENCLOSED BY '\"']
[ESCAPED BY '\\']
]
[LINES
[TERMINATED BY '/n']
]";
$ok = mysql_query($query, $go) or die(mysql_error());
$headnum = mysql_num_rows($ok);
?>

<? include('../foot.php'); ?>

error message

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'Inventory_Availabili

i tried to simplify it as much as possible

<? include('../head.php'); ?>

<?php require_once('../../Connections/go.php'); ?>
<?php
$filename = "http://file/companyportal/stock/Inventory_Availability_Report.csv";
mysql_select_db($database_go, $go);
$query = "LOAD DATA INFILE '$filename' INTO TABLE portal.stock2;";
$ok = mysql_query($query, $go) or die(mysql_error());
$headnum = mysql_num_rows($ok);
?>

<? include('../foot.php'); ?>

just to see if i could get any results.

but now it just keeps telling me this:
File 'http:\\file\companyportal\stock\Inventory_Availability_Report.csv' not found (Errcode: 22)

ah ok i had to put the relative path on the server... makes sence now

almost got it... im just getting a syntax error for something

I want it to replace entries that already exsist. (which should be every single one) but the syntax is having a problem with the [REPLACE | IGNORE] part of the query

<? include('../head.php'); ?>

<?php require_once('../../Connections/go.php'); ?>
<?php
$filename = "D:/WebDev/Intranet/companyportal/Stock/Inventory_Availability_Report.csv";
mysql_select_db($database_go, $go);
$query = "LOAD DATA INFILE '$filename' INTO TABLE portal.stock2 [REPLACE | IGNORE]
[FIELDS
[TERMINATED BY ',']
[[OPTIONALLY] ENCLOSED BY '\"']
[ESCAPED BY '\\']
]
[LINES
[TERMINATED BY '/n']
];";
$ok = mysql_query($query, $go) or die(mysql_error());

?>

<? include('../foot.php'); ?>

holy crap, sorry im retarded today

<? include('../head.php'); ?>

<?php require_once('../../Connections/go.php'); ?>
<?php
$filename = "D:/WebDev/Intranet/companyportal/Stock/Inventory_Availability_Report.csv";
mysql_select_db($database_go, $go);
$query = "load data infile '$filename' replace into table stock2 fields terminated by ',' optionally enclosed by '\"';";
$ok = mysql_query($query, $go) or die(mysql_error());

?>

<? include('../foot.php'); ?>